const oracledb = require('oracledb')

const xlsx = require('node-xlsx');
var fs = require('fs');

var moment = require('moment');

const DB_CONFIG = {
  user: 'cgxzcuser', 
  password: 'Tp3D1rBz#cx2024',
  //connectString: 'jdbc:oracle:thin:@172.50.100.181:1521:cgxzc' // 数据库地址：{IP:PORT/数据库名称}
  connectString: '172.50.100.181:1521/cgxzc' // 数据库地址：{IP:PORT/数据库名称}
}


console.log('运行模式:', oracledb.thin ? 'Thin' : 'Thick');

//oracledb.initOracleClient();
oracledb.initOracleClient({libDir: "C:\\Users\\15371\\scoop\\apps\\oracle-instant-client\\current"});

// oracledb.getConnection(DB_CONFIG).then( async connection => {
//   console.log('Connected successfully!');
//   // 执行 SQL 查询
//    result = await connection.execute(`SELECT * FROM AA_AARF`);
//       console.log('result:',result)
// }).catch(err => {
//   console.error('Connection error:', err);
// });

const 新产销与老产销=`

SELECT * FROM (
                  select
                      S1.ORDER_NO as 产销订单号
                       ,S1.ORDER_SEQ as 产销订单子项号
                       ,S1.ORDER_NO_OLD as 老产销合同号
                       ,S1.order_item_num as 老产销合同子项
                       ,S1.ORDER_STATUS as 产销订单状态
                       ,s2.合同行状态
                       ,s2.销售合同接收处理状态
                       ,s2.制造部接收状态
--        ,S1.ORDER_STATUS_CODE
                       ,S1.PROD_SPEC_CODE as 产品规范
--        ,S2.ORDER_LN_STATUS
                       ,CASE WHEN NVL(S1.CONTRACT_NO_CUST,'N/A')=客户合同号 THEN '相同' else S1.CONTRACT_NO_CUST ||' ；老：'||客户合同号 end  as  客户合同号
                       ,S1.ORDER_CLASSIFY as 分类
                       ,CASE WHEN to_char(S1.ORDER_TYPE)=合同类型描述 THEN '相同' else to_char(S1.ORDER_TYPE) ||' ；老：'||合同类型描述 end  as  合同类型描述
                       ,CASE WHEN to_char(S1.SALE_INS)=销售组织 THEN '相同' else to_char(S1.SALE_INS) ||' ；老：'||销售组织 end  as  销售组织
                       ,CASE WHEN to_char(S1.SALE_ORG)=销售区域 THEN '相同' else to_char(S1.SALE_ORG) ||' ；老：'||销售区域 end  as  销售区域
                       ,CASE WHEN to_char(S1.SALESMAN)=创建人 THEN '相同' else to_char(S1.SALESMAN) ||' ；老：'||创建人 end  as  创建人
                       ,CASE WHEN to_char(S1.DOCUMENTARY_NAME)=跟单人 THEN '相同'
                             WHEN NVL(S1.DOCUMENTARY_NAME, 'H')=NVL(跟单人, 'H') THEN '相同'
                             else to_char(S1.DOCUMENTARY_NAME) ||' ；老：'||跟单人 end  as  跟单人
                       ,CASE WHEN to_char(S1.SIGN_DATE)= TO_CHAR(签订日期, 'YYYY-MM-DD') THEN '相同' else to_char(S1.SIGN_DATE) ||' ；老：'||TO_CHAR(签订日期, 'YYYY-MM-DD') end  as  签订日期
                       ,S1.BUSINESS_TYPE as 是否出口
                       ,CASE WHEN to_char(S1.SIGN_ADDRESS)=签订地点 THEN '相同' else to_char(S1.SIGN_ADDRESS) ||' ；老：'||签订地点 end as 签订地点
                       ,CASE WHEN to_char(S1.COMPANY_ID)=首次受票人 THEN '相同' else to_char(S1.COMPANY_ID) ||' ；老：'||首次受票人 end  as  首次受票人
                       ,CASE WHEN to_char(S1.CUSTOMER_NM)=客户名称 THEN '相同'
                             when md1.code = S1.CUSTOMER_NO then '相同'
                             WHEN s2.CUSTOMER_NO = '8000000617' THEN '相同'
                             WHEN s2.CUSTOMER_NO = '1440002305' THEN '相同'
                             else to_char(S1.CUSTOMER_NM) ||' ；老：'||客户名称 end  as  客户名称
                       ,CASE WHEN to_char(S1.STLMT_UNIT_NM)=结算单位名称 THEN '相同'
                             when md2.code = S1.STLMT_UNIT_NO then '相同'
                             WHEN S2.STLMT_UNIT_NO = '8000000617' THEN '相同'
                             WHEN S2.STLMT_UNIT_NO = '1440002305' THEN '相同'
                             else to_char(S1.STLMT_UNIT_NM) ||' ；老：'||结算单位名称 end  as  结算单位名称
                       ,S1.PRODUCE_PLACE
                       ,CASE WHEN to_char(S1.SUPPLY_UNIT)=制造商 THEN '相同' else to_char(S1.SUPPLY_UNIT) ||' ；老：'||制造商 end as 制造商
                       ,S1.FREIGHT_MODE
                       ,S1.INVOICE_TYPE
                       ,S1.BILL_DATE
                       ,CASE WHEN NVL(S1.PRICE_FL,'N/A')=计价方式 THEN '相同' else to_char(S1.PRICE_FL) ||' ；老：'||计价方式 end  as  计价方式
                       ,CASE WHEN to_char(S1.CURRENCY)=币种 THEN '相同' else to_char(S1.CURRENCY) ||' ；老：'||币种 end  as  币种
                       ,CASE WHEN NVL(S1.MNY_ASK_TYP,'N/A')=付款方式 THEN '相同' else to_char(S1.MNY_ASK_TYP) ||' ；老：'||付款方式 end  as  付款方式
                       ,S1.MONTH
                       ,S1.GROUP_ORDER_NO
                       ,S1.PROD_SPEC
                       ,S1.APN_CODE
                       ,S1.APN_DEF
                       ,S1.PROD_NAME  as  品名描述
                       ,S1.QLY_GRADE_NO  as  标准类别描述
                       ,S1.TRADE_NO  as  钢级名称
                       ,S1.PROD_STD  as  标准名称
                       ,S1.PIPE_TYPE as 管端类型
                       ,S1.MANU_METHOD as 制造方式
                       ,S1.DELIVERY_STATUS_NAME as 交货状态
                       ,CASE WHEN to_char(S1.SALE_PROD_CODE_NAME)=品名描述 THEN '相同' else to_char(S1.SALE_PROD_CODE_NAME) ||' ；老：'||品名描述 end  as  订货品名描述
                       ,CASE WHEN S1.PROD_SPEC_CODE<>'99999' then  '相同' when  to_char(S1.SALE_SG_STD_NM)=标准名称 THEN '相同' else to_char(S1.SALE_SG_STD_NM) ||' ；老：'||标准名称 end  as  订货标准名称
                       , CASE
                             WHEN to_char(S1.SALE_SHOP_SIGN_NAME) = 'X42NS/BNS/Gr.B' and 钢级名称 = 'BNS/X42NS/Gr.B' then '相同'
                             WHEN to_char(S1.SALE_SHOP_SIGN_NAME) = 'X42QS/BQS/Gr.B' and 钢级名称 = 'BQS/X42QS/Gr.B' then '相同'
                             WHEN to_char(S1.SALE_SHOP_SIGN_NAME) = 钢级名称 THEN '相同'
                             else to_char(S1.SALE_SHOP_SIGN_NAME) || ' ；老：' || 钢级名称 end as 订货钢级名称
                       ,CASE WHEN S1.PROD_SPEC_CODE<>'99999' and  S1.SALE_GOOD_STATUS_NAME is not   null then '相同' when S1.PROD_SPEC_CODE='99999' and  S1.SALE_GOOD_STATUS_NAME is null then '相同'  else to_char(S1.DELIVERY_STATUS_NAME) end as  订货交货状态
                       ,CASE WHEN S1.OUT_DIA=外径 THEN '相同'
                             when S1.PROD_TYPE_NO = 'CQ' and  NVL(S1.OUT_DIA,0) != 0  then '相同'
                             else S1.OUT_DIA ||' ；老：'||外径 end  as  外径对比
                       ,CASE WHEN S1.WAL_THK=壁厚 THEN '相同'
                             when S1.PROD_TYPE_NO = 'CQ' and  NVL(S1.WAL_THK,0) != 0  then '相同'
                             else S1.WAL_THK ||' ；老：'||壁厚 end  as  壁厚对比
                       ,CASE WHEN  S1.PROD_SPEC_CODE<>'99999' then '相同' when  to_char(S1.MODEL_NAME)=扣型描述 THEN '相同' else to_char(S1.MODEL_NAME) ||' ；老：'||扣型描述 end  as  扣型描述
                       ,产品规格描述 AS "产品规格描述(老)"
                       ,S1.SPEC_CODE_NAME AS "产品规格描述(新)"
                       ,CASE WHEN to_char(S1.SPEC_CODE_NAME)=产品规格描述 THEN '相同'
                             WHEN to_char(REPLACE(S1.SPEC_CODE_NAME,'*','x'))=产品规格描述 THEN '相同'
                             else to_char(S1.SPEC_CODE_NAME) ||' ；老：'||产品规格描述 end  as  产品规格描述
                       ,CASE WHEN to_char(S1.ORDER_SPEC_CODE_NAME)=订货规格描述 THEN '相同' else to_char(S1.ORDER_SPEC_CODE_NAME) ||' ；老：'||订货规格描述 end  as  订货规格描述
                       ,CASE WHEN S1.WGT=交货量 THEN '相同'	else S1.WGT ||' ；老：'||交货量 end  as  交货量
                       ,CASE WHEN to_char(S1.UNIT)=订货计量单位 THEN '相同' else to_char(S1.UNIT) ||' ；老：'||订货计量单位 end  as  订货计量单位
                       ,CASE WHEN S1.LEN_MIN=下限 THEN '相同' else S1.LEN_MIN ||' ；老：'||下限 end  as  长度下限
                       ,CASE WHEN S1.LEN_MAX=上限 THEN '相同' else S1.LEN_MAX ||' ；老：'||上限 end  as  长度上限
                       ,CASE WHEN to_char(S1.LEN_UNIT)=长度单位 THEN '相同' else to_char(S1.LEN_UNIT) ||' ；老：'||长度单位 end  as  长度单位
                       ,CASE WHEN NVL(S1.ORD_ENSU_TOTL_LEN,0)=NVL(保总长,0) THEN '相同' else S1.ORD_ENSU_TOTL_LEN ||' ；老：'||保总长 end  as  保总长
                       ,CASE WHEN nvl(S1.SHRT_LEN_RATO,0)=nvl(短尺率,0) THEN '相同' else S1.SHRT_LEN_RATO ||' ；老：'||短尺率 end  as  短尺率
                       ,CASE WHEN S1.SHORTEST=nvl(短尺最短,0) THEN '相同' else S1.SHORTEST ||' ；老：'||短尺最短 end  as  短尺最短
                       ,CASE when nvl(S1.SHRT_LEN_RATO,0)>0 and to_char(S1.SHRT_STOVE)='1' THEN '相同'  when to_char(S1.SHRT_STOVE)=短尺随炉 then '相同' else to_char(S1.SHRT_STOVE) ||' ；老：'||短尺随炉 end  as  短尺随炉
                       ,CASE WHEN S1.AVG_LEN_MIN=nvl(平均长度大于,0) THEN '相同' else S1.AVG_LEN_MIN ||' ；老：'||平均长度大于 end  as  平均长度大于
                       ,CASE WHEN S1.AVG_LEN_MAX=nvl(平均长度小于,0) THEN '相同' else S1.AVG_LEN_MAX ||' ；老：'||平均长度小于 end  as  平均长度小于
                       ,CASE WHEN S1.DELVRY_RANGE_MIN=交货量差下限*-1 THEN '相同'
                             when NVL(S1.DELVRY_RANGE_MIN, 0) < 0 AND NVL(交货量差下限,0) = ABS(NVL(S1.DELVRY_RANGE_MIN, 0))  then '相同'
                             else S1.DELVRY_RANGE_MIN ||' ；老：'||交货量差下限*-1 end  as  交货量差下限
                       ,CASE WHEN S1.DELVRY_RANGE_MAX=交货量差上限 THEN '相同' else S1.DELVRY_RANGE_MAX ||' ；老：'||交货量差上限 end  as  交货量差上限
                       ,CASE WHEN nvl(S1.SIGL_LEN,0)=nvl(单倍尺长,0) THEN '相同' else S1.SIGL_LEN ||' ；老：'||单倍尺长 end  as  单倍尺长
--         ,s1.DELVRY_EDATE ||' ；老：'||TO_CHAR(交货结束日期, 'YYYY-MM-DD')  as  交货结束日期
                       ,CASE WHEN TO_CHAR(s1.DELVRY_EDATE) =TO_CHAR(交货结束日期, 'YYYY-MM-DD') THEN '相同' else TO_CHAR(s1.DELVRY_EDATE) ||' ；老：'||TO_CHAR(交货结束日期, 'YYYY-MM-DD') end  as  交货结束日期
                       ,CASE WHEN to_char(S1.CONSIGNEE_COM)=收货单位名称 THEN '相同' else to_char(S1.CONSIGNEE_COM) ||' ；老：'||收货单位名称 end  as  收货单位名称
                       ,CASE WHEN to_char(S1.TRANS_TYPE)=出厂方式 THEN '相同' else to_char(S1.TRANS_TYPE) ||' ；老：'||出厂方式 end  as  出厂方式
                       ,CASE when S1.BUSINESS_TYPE='1' then '相同' WHEN to_char(S1.TRANS_MODE)=代办标识 THEN '相同' else to_char(S1.TRANS_MODE) ||' ；老：'||代办标识 end  as  代办标识
                       ,S1.STATION_NAME
                       ,S1.SPCL_LN_NM
                       ,S1.MEMO
                       ,CASE WHEN to_char(S1.FINAL_USER_NM)=最终用户 THEN '相同'
                             when md3.code = S1.FINAL_USER then '相同'
                             WHEN S2.FINAL_USER = '8000000617' THEN '相同'
                             else to_char(S1.FINAL_USER_NM) ||' ；老：'||最终用户 end  as  最终用户
                       ,CASE WHEN to_char(S1.QUALITY_USER)=质保书单位 THEN '相同'
                             WHEN to_char(S1.QUALITY_USER) = '订货客户' AND 质保书单位 = '订货单位' THEN '相同'
                             else to_char(S1.QUALITY_USER) ||' ；老：'||质保书单位 end  as  质保书单位
                       ,CASE WHEN to_char(S1.OUTBOUND_CALCULATE)=出库计量 THEN '相同' else to_char(S1.OUTBOUND_CALCULATE) ||' ；老：'||出库计量 end  as  出库计量
                       ,CASE WHEN to_char(S1.OUTS_BILLET_FL)=外购坯 THEN '相同' else to_char(S1.OUTS_BILLET_FL) ||' ；老：'||外购坯 end  as  外购坯
                       ,CASE WHEN to_char(nvl(S1.OUTS_PIPE,'/'))=外购管 THEN '相同' else to_char(S1.OUTS_PIPE) ||' ；老：'||外购管 end  as  外购管
                       ,CASE WHEN to_char(S1.MONITOR_FLAG)=监制要求 THEN '相同' else to_char(S1.MONITOR_FLAG) ||' ；老：'||监制要求 end  as  监制要求
                       ,CASE WHEN to_char(S1.MAN_INSPECTION)=在线机探盲区 THEN '相同' else to_char(S1.MAN_INSPECTION) ||' ；老：'||在线机探盲区 end  as  在线机探盲区
                       ,case when to_char(NVL(S1.INSPECTION_VALUE,0)) = NVL(S2.INSPECTION_VALUE,'0')  then '相同' else to_char(S1.INSPECTION_VALUE) ||',新产销：'|| to_char(S2.INSPECTION_VALUE) end 		在线机探盲区切长
                       ,CASE WHEN to_char(S1.COOPERATE_FLAG)=是否协同 THEN '相同' else to_char(S1.COOPERATE_FLAG) ||' ；老：'||是否协同 end  as  是否协同
                       ,CASE WHEN to_char(S1.EXTRA_REQUIRE_OLD)=订货附加要求 THEN '相同' else '不同' end  as  订货附加要求
                       ,CASE WHEN to_char(S1.PRO_TEC_REQUIRE_OLD)=特殊要求技术规范 THEN '相同' else '不同' end  as  特殊要求技术规范
                       ,CASE WHEN to_char(S1.KEEP_TYPE)=保价类型 THEN '相同'
                             WHEN NVL(to_char(S1.KEEP_TYPE),'无')=NVL(保价类型, '无') THEN '相同'
                             else to_char(S1.KEEP_TYPE) ||' ；老：'||保价类型 end  as  保价类型
                       ,CASE WHEN nvl(S1.FREIGHT,0)=ROUND(nvl(运费,0), 3) THEN '相同' else S1.FREIGHT ||' ；老：'||运费 end  as  运费
                       ,CASE WHEN nvl(S1.OUT_PRICE,0)=ROUND(nvl(出厂价,0),4) THEN '相同' else S1.OUT_PRICE ||' ；老：'||出厂价 end  as  出厂价
                       ,CASE WHEN nvl(S1.PAK_PRICE,0)=nvl(吊装费,0) THEN '相同' else S1.PAK_PRICE ||' ；老：'||吊装费 end  as  吊装费
                       ,CASE WHEN nvl(S1.BALNCE_PRICE,0)=ROUND(nvl(结算价,0),4) THEN '相同' else S1.BALNCE_PRICE ||' ；老：'||结算价 end  as  结算价
                       ,s2.合同状态
                       ,s2.MPS要求
                       ,s2.MPS编号
                       ,s2.MPS说明
                       ,s2.运输方式
                       ,s2.输出标准号
                       ,s2.交货地点
                       ,s2.指定地址
                       ,s2.自提标志
                  from (



                           SELECT
                               t2.id,
                               t1.order_no,--合同号
                               t1.ORDER_NO_OLD,--老产销订单
                               t2.ORDER_NO_OLD || '/' || t2.ORDER_SEQ_OLD || '/' || t2.DELIVERY_NO_OLD AS order_item_num,
                               t1.CONTRACT_NO_CUST,--客户合同号
                               t1.TRADE_TYPE,--贸易类型
                               t1.CUSTOMER_NO,
                               t1.STLMT_UNIT_NO,
                               t2.PROD_TYPE_NO,
                               t2.INSPECTION_VALUE,
                               t2.ORDER_STATUS  ORDER_STATUS_CODE,
                               COALESCE(sdc1.STR_VALUE, t1.ORDER_CLASSIFY) ORDER_CLASSIFY,--分类
                               sdc2.STR_VALUE ORDER_MODE,--合同类型
                               decode(sdc3.STR_VALUE,'期货','自产期货','预排','预排合同',sdc3.STR_VALUE) ORDER_TYPE,
--     CASE
--         WHEN sdc3.STR_VALUE = '期货' THEN '自产期货'
--         WHEN sdc3.STR_VALUE = '预排' THEN '预排合同'
--         ELSE sdc3.STR_VALUE
--         END ORDER_TYPE,--分类--订货方式
                               sdc4.STR_VALUE SALE_INS,--销售组织
                               hr_dep.STR_NAME SALE_ORG,--片区
                               COALESCE(hr_sales.NAME, t1.SALESMAN) SALESMAN,--业务员
                               COALESCE(hr_doc.NAME, t1.DOCUMENTARY_NAME) DOCUMENTARY_NAME,--跟单人
                               t1.SIGN_DATE,--签订日期
                               CASE WHEN t1.BUSINESS_TYPE = '1' THEN '出口' ELSE '非出口' END BUSINESS_TYPE,--业务分类
                               t1.SIGN_ADDRESS,--签订地点
                               decode(sdc5.STR_VALUE,'钢贸','天津钢管钢铁贸易有限公司','国贸','天津钢管国际经济贸易有限公司','制造','天津钢管制造有限公司',sdc5.STR_VALUE) COMPANY_ID,
--     CASE
--         WHEN sdc5.STR_VALUE = '钢贸' THEN '天津钢管钢铁贸易有限公司'
--         WHEN sdc5.STR_VALUE = '国贸' THEN '天津钢管国际经济贸易有限公司'
--         WHEN sdc5.STR_VALUE = '制造' THEN '天津钢管制造有限公司'
--         ELSE sdc5.STR_VALUE
--         END COMPANY_ID,--卖出单位
                               t1.CUSTOMER_NM,--订货单位
                               t1.STLMT_UNIT_NM,--结算单位
                               decode(t1.PRODUCE_PLACE,'Z','自产','外购') PRODUCE_PLACE,
                               decode(sdc6.STR_VALUE,'制造','天津钢管制造有限公司',sdc6.STR_VALUE) SUPPLY_UNIT,
--     CASE WHEN t1.PRODUCE_PLACE = 'Z' THEN '自产' ELSE '外购' END PRODUCE_PLACE,--产地区分
--     CASE
--         WHEN sdc6.STR_VALUE = '制造' THEN '天津钢管制造有限公司'
--         ELSE sdc6.STR_VALUE
--         END SUPPLY_UNIT,--制造商
                               sdc7.STR_VALUE FREIGHT_MODE,--运费开票方式
                               sdc8.STR_VALUE INVOICE_TYPE,--发票类型
                               sdc9.STR_VALUE BILL_DATE,
                               t1.BANK,--开户行
                               t1.ACCOUNT,--开户行账号
                               t1.TAX_ID,--税号
                               sdc10.STR_VALUE PRICE_FL,--定价方式
                               sdc11.STR_VALUE CURRENCY,--币种
                               sdc12.STR_VALUE MNY_ASK_TYP,--付款方式
                               t1.PRICE_PLAN,--优惠方案
                               t1.ORDER_REQUIRE_STATUS,--订单要求
                               t1.PORT_OF_DESTINATION,--目的港
                               t2.ORDER_SEQ,--合同行
                               t2.ORDER_SEQ_OLD,--老产销合同行
                               t2.DELIVERY_NO_OLD,--老产销交货行
                               sdc13.STR_VALUE ORDER_STATUS,--状态
                               t2.MONTH,--排产月份
                               t2.GROUP_ORDER_NO||'/'||GROUP_ORDER_SEQ GROUP_ORDER_NO, --集团订单号
                               t2.PROD_SPEC_CODE,--规范码
                               t2.PROD_SPEC,--产品规范描述
                               t2.APN_CODE,--用途码代码
                               t2.APN_DEF,--用途码描述
                               t2.PROD_NAME,--规范品名
                               sdc14.STR_VALUE QLY_GRADE_NO,--质量等级
                               t2.TRADE_NO,--规范钢级
                               t2.PROD_STD,--规范标准
                               sdc15.STR_VALUE PIPE_TYPE,--管端类型
                               sdc16.STR_VALUE MANU_METHOD,--制造方式
                               t2.DELIVERY_STATUS_NAME,--规范交货状态
                               t2.SALE_PROD_CODE_NAME,--订货品名
                               t2.SALE_SG_STD_NM,--订货标准
                               t2.SALE_SHOP_SIGN_NAME,--订货钢级
                               t2.SALE_GOOD_STATUS_NAME,--订货交货状态
                               t2.OUT_DIA,--外径
                               t2.WAL_THK,--壁厚
                               t2.MODEL_NAME,--扣型
                               t2.SPEC_CODE_NAME,--产品规格
                               t2.ORDER_SPEC_CODE_NAME,--订货规格
                               t2.WGT,--订货量
                               sdc17.STR_VALUE UNIT,--订货单位
                               t2.LEN_MIN,--长度下限
                               t2.LEN_MAX,--长度上限
                               sdc18.STR_VALUE LEN_UNIT,--长度单位
                               t2.ORD_ENSU_TOTL_LEN,--保总长
                               t2.SHRT_LEN_RATO,--短尺率
                               t2.SHORTEST,--短尺最短
                               t2.SHRT_STOVE,--短尺随炉
                               t2.AVG_LEN_MIN,--平均长度大于
                               t2.AVG_LEN_MAX,--平均长度小于
                               t2.DELVRY_RANGE_MIN,--交货量差下限
                               t2.DELVRY_RANGE_MAX,--交货量差上限
                               t2.SIGL_LEN,--单倍长度
                               t2.DELVRY_EDATE,--交货期
                               t2.CONSIGNEE_COM,--收货单位
                               sdc19.STR_VALUE TRANS_TYPE,--运输方式
                               decode(sdc20.STR_VALUE,'代办','待办','自提-销售承运','自提',sdc20.STR_VALUE) TRANS_MODE,
--     CASE
--         WHEN sdc20.STR_VALUE = '代办' THEN '待办'
--         WHEN sdc20.STR_VALUE = '自提-销售承运' THEN '自提'
--         ELSE sdc20.STR_VALUE
--         END TRANS_MODE,--运输模式
                               t2.STATION_NAME,--到站
                               t2.SPCL_LN_NM,--专用线
                               t2.MEMO,--备注
                               t2.FINAL_USER_NM,--最终用户
                               t2.FINAL_USER,
                               sdc21.STR_VALUE QUALITY_USER,--质保书单位
                               sdc22.STR_VALUE OUTBOUND_CALCULATE,--出库计量
                               CASE WHEN OUTS_BILLET_FL = '1' THEN '外购' ELSE '自炼' END OUTS_BILLET_FL,--外购坯
                               sdc23.STR_VALUE OUTS_PIPE,--外购管
                               CASE WHEN MONITOR_FLAG = '1' THEN '监制' ELSE '不监制' END MONITOR_FLAG,--是否监制
                               sdc24.STR_VALUE MAN_INSPECTION,--在线机探盲区
                               t2.PROJECT_NO,--订单工程编号
                               CASE WHEN COOPERATE_FLAG = '1' THEN '是' ELSE '否' END COOPERATE_FLAG,--是否协同
                               t2.EXTRA_REQUIRE_OLD,--合同附加要求
                               t2.PRO_TEC_REQUIRE_OLD,--产品技术要求
                               sdc25.STR_VALUE KEEP_TYPE,--保价类型
                               t2.FREIGHT,--运费
                               t2.OUT_PRICE,--出厂价
                               t2.PAK_PRICE,--吊装费
                               t2.BALNCE_PRICE--结算价
                           FROM SO_ORDER@CGXZCDB_ZSJ t1
                                    LEFT JOIN SO_ORDER_dtl@CGXZCDB_ZSJ t2 ON t1.order_no = t2.order_no
-- 字典表JOIN优化
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc1 ON sdc1.DICT_SN = 'ORDER_CLASSIFY' AND sdc1.STR_KEY = t1.ORDER_CLASSIFY
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc2 ON sdc2.DICT_SN = 'ORDER_MODE' AND sdc2.STR_KEY = t1.ORDER_MODE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc3 ON sdc3.DICT_SN = 'ORDER_TYPE' AND sdc3.STR_KEY = t1.ORDER_TYPE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc4 ON sdc4.DICT_SN = 'saleIns' AND sdc4.STR_KEY = t1.SALE_INS
                                    LEFT JOIN hr_department@CGXZCDB_ZSJ hr_dep ON hr_dep.STR_SN = t1.SALE_ORG
                                    LEFT JOIN HR_USER@CGXZCDB_ZSJ hr_sales ON hr_sales.USER_NO = t1.SALESMAN
                                    LEFT JOIN HR_USER@CGXZCDB_ZSJ hr_doc ON hr_doc.USER_NO = t1.DOCUMENTARY_NAME
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc5 ON sdc5.DICT_SN = 'saleOrg' AND sdc5.STR_KEY = t1.COMPANY_ID
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc6 ON sdc6.DICT_SN = 'SUPPLY_UNIT' AND sdc6.STR_KEY = t1.SUPPLY_UNIT
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc7 ON sdc7.DICT_SN = 'SETTLE_FREIGHT_MODE' AND sdc7.STR_KEY = t1.FREIGHT_MODE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc8 ON sdc8.DICT_SN = 'ORDER_INVOICE_TYPE' AND sdc8.STR_KEY = t1.INVOICE_TYPE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc9 ON sdc9.DICT_SN = 'saleBillDate' AND sdc9.STR_KEY = t1.BILL_DATE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc10 ON sdc10.DICT_SN = 'PRICE_FL' AND sdc10.STR_KEY = t1.PRICE_FL
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc11 ON sdc11.DICT_SN = 'currency' AND sdc11.STR_KEY = t1.CURRENCY
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc12 ON sdc12.DICT_SN = 'MNY_ASK_TYP' AND sdc12.STR_KEY = t1.MNY_ASK_TYP
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc13 ON sdc13.DICT_SN = 'orderDtlStatus' AND sdc13.STR_KEY = t2.ORDER_STATUS
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc14 ON sdc14.DICT_SN = 'qualityLevels' AND sdc14.STR_VALUE2 = t2.QLY_GRADE_NO
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc15 ON sdc15.DICT_SN = 'pipeType' AND sdc15.STR_KEY = t2.PIPE_TYPE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc16 ON sdc16.DICT_SN = 'manuMethod' AND sdc16.STR_KEY = t2.MANU_METHOD
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc17 ON sdc17.DICT_SN = 'orderUnit' AND sdc17.STR_KEY = t2.UNIT
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc18 ON sdc18.DICT_SN = 'LEN_UNIT' AND sdc18.STR_KEY = t2.LEN_UNIT
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc19 ON sdc19.DICT_SN = 'TRANS_TYPE' AND sdc19.STR_KEY = t2.TRANS_TYPE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc20 ON sdc20.DICT_SN = 'transMode' AND sdc20.STR_KEY = t2.TRANS_MODE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc21 ON sdc21.DICT_SN = 'so_order_qualityUser' AND sdc21.STR_KEY = t2.QUALITY_USER
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc22 ON sdc22.DICT_SN = 'OUTBOUND_CALCULATE' AND sdc22.STR_KEY = t2.OUTBOUND_CALCULATE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc23 ON sdc23.DICT_SN = 'order_dtl_outs_type' AND sdc23.STR_KEY = t2.OUTS_PIPE
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc24 ON sdc24.DICT_SN = 'MAN_INSPECTION' AND sdc24.STR_KEY = t2.MAN_INSPECTION
                                    LEFT JOIN SYS_DICTIONARY_DTL@CGXZCDB_ZSJ sdc25 ON sdc25.DICT_SN = 'PROTECT_TYPE' AND sdc25.STR_KEY = t2.KEEP_TYPE
                           WHERE t1.order_status<>'XX'
                             AND t2.order_status<>'XX'
                             AND t2.order_status='67'
                             AND t1.order_type<>'X'
                             AND t1.ORDER_SPECIES='A'





--                                                                                                          and  t1.ORDER_NO_old = 'XSXPQ-25A1468'
                       ) s1 left JOIN
                       (SELECT
                            t.ORDER_NO AS 合同号,
                            T1.ORDER_SEQ AS 合同行号,
                            T2.DELIVERY_NO AS 交货行号,
                            t.CUSTM_ORDER_NO AS 客户合同号,
                            t.ORDER_TYP_DESC AS 合同类型描述,
                            t.CUSTOMER_NM AS 客户名称,
                            t.CUSTOMER_NO,
                            t1.INSPECTION_VALUE,
                            t1.ORDER_LN_STATUS,
                            t.SIGN_DATE AS 签订日期,
                            t.SIGN_ADDRESS as 签订地点,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.CURRENCY ) AS 币种,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.PRICE_FL ) AS 计价方式,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.MNY_ASK_TYP ) AS 付款方式,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t.SUPPLY_UNIT ) AS 制造商,
                            t.STLMT_UNIT_NM AS 结算单位名称,
                            t.STLMT_UNIT_NO,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.FST_DRAWEE_UNIT ) AS 首次受票人,
                            t.MNY_ASK_DESC AS 付款要求描述,
                            t.SALE_ORG_DESC AS 销售组织,
                            t.SALE_AREA_DESC AS 销售区域,
                            t.ORDER_STS_DESC AS 合同状态,
                            t.EFFECT_MAN AS 生效人,
                            t.CREATE_NAME AS 创建人,
                            t.UPDATE_NAME AS 修改人,
                            t.DOCUMENTARY_NAME AS 跟单人,
                            DECODE( t.CONTAIN_TAX, '1', '是', '否' ) AS 是否含税 ,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T.ORDER_CLASSIFY ) AS 合同分类 ,
                            t.PRINT_PRICE AS 打印价格,
                            t.PRICE_PLAN AS 优惠方案,
                            DECODE( t.ORDER_ARCHIVE, '1', '是', '否' ) AS 合同归档,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.PRD_CLS ) AS 品种分类,
                            T1.PSC_DESC AS 产品规范描述,
                            T1.MSC_DESC AS 冶金规范描述,
                            T1.USE_DESC AS 用途码描述,
                            T1.PRODUCNAME AS 品名描述,
                            T1.STD_NAME AS 标准名称,
                            T1.STEELNAME AS 钢级名称,
                            T1.STD_STYLE_DESC AS 标准类别描述,
                            T1.ORDER_SPEC_DESC AS 订货规格描述,
                            T1.SPEC_NAME AS 产品规格描述,
                            T1.DIMATER AS 外径,
                            T1.HEIGHT AS 壁厚,
                            T1.MODEL_DESC AS 扣型描述,
                            T1.LEN_DESC AS 长度描述,
                            T1.LEN_UNIT AS 长度单位,
                            T1.LEN_MIN AS 下限,
                            T1.LEN_MAX AS 上限,
                            T1.SHRT_LEN_RATO AS 短尺率,
                            T1.SHORTEST AS 短尺最短,
                            T1.LONG_LEN_RATO AS 长尺率,
                            T1.LONGEST AS 长尺最长,
                            case when T1.SHRT_STOVE is null or T1.SHRT_STOVE ='/' then '0' else T1.SHRT_STOVE end AS 短尺随炉,
                            T1.AVG_LEN_MIN AS 平均长度大于,
                            T1.AVG_LEN_MAX AS 平均长度小于,
                            T1.ENDLEN_SPECSTD AS 成品公差,
                            T1.SIGL_LEN AS 单倍尺长,
                            T1.ORDER_UNIT AS 订货计量单位,
                            T1.ORDER_QTY AS 订货量,
                            T1.ORD_ENSU_TOTL_LEN AS 订货保总长,
                            T1.PRDCT_WGT AS 生产吨数,
                            T1.DELVRY_RANGE_TPE AS 交货量差方式,
                            T1.DELVRY_RANGE_MIN AS 交货量差下限,
                            T1.DELVRY_RANGE_MAX AS 交货量差上限,
                            T1.FINAL_USER_DESC AS 最终用户,
                            T1.FINAL_USER,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_BILLET_FL ) AS 外购坯,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTS_STL_PIPE_FL ) AS 外购管,
                            DECODE( T1.ASSEL_FL, '1', '允许', '不允许' ) AS ASSEL,
                            DECODE( T1.PRODUCER_FL, '1', '监制', '不监制' ) AS 监制要求,
                            T1.PRDCR_DESC AS 监制说明,
                            DECODE( T1.MPS_FL, '1', '有', '无' ) AS MPS要求,
                            T1.MPS_NO AS MPS编号,
                            T1.MPS_DESC AS MPS说明,
                            T1.ORDER_ADD_DESC AS 订货附加要求,
                            T1.ORDER_SPECIAL_HEAD AS 特殊要求技术规范,
                            T1.ORDER_PRICE AS 订货价,
                            T1.TRANS_PRICE AS 运费,
                            T1.TEST_PRICE AS 测算价,
                            T1.OUT_PRICE AS 出厂价,
                            T1.BALNCE_PRICE AS 结算价,
                            T1.PAK_PRICE AS 吊装费,
                            T1.TON_PRICE AS 吨价,
                            DECODE( T1.KEEP_TYPE, '1', '保总价', '0', '保单价' ) AS 保价类型,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.ORDER_LN_STATUS ) AS 合同行状态,
                            --0待接收,1已接收,2已下发,3生产中,4生产完成,5缴库完成,6发运完毕,7结算完毕,8合同关闭
                            (SELECT DECODE(MIN(PORDERSTATUS), '0', '待接收', '1', '已接收', '2', '已下发', '3', '生产中', '4', '生产完成', '5', '缴库完成', '6', '发运完毕', '7', '结算完毕','8', '关闭', '9', '取消')  FROM PLN_SALEORD@TGCXDB_105   WHERE ORD_LN_DLY_PK = T2.ORD_LN_DLY_PK )  AS 销售合同接收处理状态,
                            (SELECT DECODE(MIN(PLANSTATUS), '0', '待接收', '1', '已接收', '2', '已下发', '3', '生产中', '4', '生产完成', '5', '缴库完成', '6', '发运完毕', '7', '结算完毕','8', '关闭', '9', '取消')  FROM PLN_SALEORD_PROC@TGCXDB_105 WHERE ORD_LN_DLY_PK = T2.ORD_LN_DLY_PK)  AS 制造部接收状态,
                            DECODE( T1.BALNCE_PRICE_STS, '1', '有确认', '录入' ) AS 结算价状态,
                            DECODE( T1.MAN_INSPECTION, '1', '可手探', '不可手探' ) AS 在线机探盲区,
                            T1.INSPECTION_VALUE AS 在线机探盲区切长,
                            T1.QUALITY_USER_DESC AS 质保书单位 ,
                            T1.WBS_CODE AS WBS号,
                            DECODE( T1.OUTBOUND_CALCULATE, '1', '复磅出库', '2', '实际重量交货', '0', '实际重量交货' ) 出库计量,
                            DECODE( T1.ORDER_TEAM, '1', '是', '0', '否' ) 是否协同,
                            DECODE( T1.MODE_OF_TRANSPORT, '1', '散货', '2', '集装箱' ) 运输方式,
                            DECODE( T1.AUTO_MEASURE, '1', '是', '0', '否' ) 管端自动测量 ,
                            T1.KEY_PROJECT_NAME AS 攻关项目 ,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = T1.OUTPUT_STD_NO ) 输出标准号,
                            t2.DELVRY_EDATE AS 交货结束日期,
                            t2.DELVRY_QTY AS 交货量,
                            t2.WGT_ESTMT AS 重量估算,
                            t2.ENSU_TOTL_LEN AS 保总长,
                            t2.RECEIV_NM AS 收货单位名称,
                            t2.DELVRY_ADDR AS 交货地点,
                            DECODE( t2.AGENCY_FL, '1', '待办', '0', '自提' ) AS 代办标识,
                            t2.RECEIV_ADDR AS 指定地址,
                            ( SELECT BASENAME FROM COM_BASE_INFO@TGCXDB_105 WHERE BASECODE = t2.TRANSIT_TYP ) AS 出厂方式,
                            t2.TRANSIT_PLAN AS 运输方案,
                            DECODE( t2.PICKUP_FLAG, '1', '自提', '0', '否' ) AS 自提标志
                        FROM
                            SLM_ORDER_HEAD@TGCXDB_105 t
                                INNER JOIN SLM_ORDER_LINE@TGCXDB_105 T1 ON T.ORD_PK = T1.ORD_PK
                                INNER JOIN SLM_ORDER_LINE_DELIVERY@TGCXDB_105 T2 ON T.ORD_PK = T2.ORD_PK
                                AND T1.ORD_LN_PK = T2.ORD_LN_PK
                        WHERE
                            t.VALIDFLAG = '1'  and t1.VALIDFLAG = '1' and t2.VALIDFLAG='1'
--           and T.ORDER_NO = 'XSXPQ-25A1468'
                       ) s2 on s1.order_no_old = s2.合同号 and s1.ORDER_SEQ_OLD = s2.合同行号 and s1.DELIVERY_NO_OLD=s2.交货行号

                            LEFT JOIN (
                      SELECT CUSTOMER_NO, code,
                             ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
                      FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
                  )md1 ON md1.CUSTOMER_NO = s2.CUSTOMER_NO AND md1.rn = 1
                            LEFT JOIN (
                      SELECT CUSTOMER_NO, code,
                             ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
                      FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
                  )md2 ON md2.CUSTOMER_NO = s2.STLMT_UNIT_NO AND md2.rn = 1
                            LEFT JOIN (
                      SELECT CUSTOMER_NO, code,
                             ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NO ORDER BY id DESC) AS rn
                      FROM MD_SUPPLY_OLD_SYSTEM_LINK@TGCXDB_105
                  )md3 ON md3.CUSTOMER_NO = s2.FINAL_USER AND md3.rn = 1
                  WHERE    s2.合同号 is not null -- s1.prod_spec_code = '99999' AND s1.ORDER_STATUS_CODE IN ('00','10')
              ) ss



`


  const date = moment().format('YYYY-MM-DD HH-mm');
  const dateSubFix = `99999-${date}`;
// 使用连接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();
    //   connection.execute('select * from AA_AARF ',(err,result)=>{
    //     console.log('result:',result)
    //   })
     // const 新产销与老产销Result = await 
     connection.execute(新产销与老产销,(err,result)=>{

    //    connection.execute("select * from AA_AARF",(err,result)=>{
      //  console.log('err,result:',err,result)
            const name = `新产销与老产销-67${dateSubFix}`
            var buffer = xlsx.build([
                {name:'统计',data:[result.metaData.map(s=>s.name),...result.rows]},
            ]);
             fs.writeFile(`./${name}.xlsx`,buffer,{flush:true},(err)=>{

             });
      });
    //   connection.execute(新产销与老产销+ ` where S1.PROD_SPEC_CODE != '99999'`,(err,result)=>{
    //         const name = `新产销与老产销-非${dateSubFix}`
    //         var buffer = xlsx.build([
    //             {name:'统计',data:[result.metaData.map(s=>s.name),...result.rows]},
    //         ]);
    //          fs.writeFileSync(`./${name}.xlsx`,buffer,{'flag':'w'});
    //   });
    //   connection.execute(新产销与老产销+ ` where S1.PROD_SPEC_CODE = '99999'`,(err,result)=>{
    //          const name = `新产销与老产销-${dateSubFix}`
    //         var buffer = xlsx.build([
    //              {name:'统计',data:[result.metaData.map(s=>s.name),...result.rows]},
    //         ]);
    //          fs.writeFileSync(`./${name}.xlsx`,buffer,{'flag':'w'});
    //   });

    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    console.log(err)
    throw err;
  } finally {
    await pool.close();
  }
}
run()